﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using OpenSISDataConnection;
using OSISEthnicityBusinessLogic;
using System.Data;

namespace OSISEthnicityDataLayer
{
   public class EthnicityDL
    {
       public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

       public EthnicityDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }

       public void AddEthnicitys(EthnicityBL Ethnicity)
       {
           if (server == "MSSQLServer")
           {
               DataSet ds = null;
               DataSet dsShortOrder = null;
               ds = DataConnection.ConnectionPath.Getdata("exec getMax eth_id,ethnicity");

               dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(short_order),0) + 1 as short_order from ethnicity");

               String strAddEthnicity = "INSERT into ethnicity(eth_id,eth_cd,eth_nm,last_updated,eth_flag,short_order,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Ethnicity.EthnicityShortName.Replace("'", "''") + "',N'" + Ethnicity.EthnicityName.Replace("'", "''") + "',N'" + Ethnicity.LastUpdated + "',N'A'," + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + ","+ Ethnicity.SchoolID +")";

               DataConnection.ConnectionPath.ExecuteCommand(strAddEthnicity);


           }



           if (server == "MySQL")
           {
               DataSet ds = null;
               ds = DataConnection.ConnectionPath.Getdata("CALL getmax('eth_id','ethnicity')");

               DateTime s = Convert.ToDateTime(Ethnicity.LastUpdated, DateTimeFormatInfo.InvariantInfo);
               Ethnicity.LastUpdated = s.ToString("yyyy/MM/dd");

               DataSet dsShortOrder = null;
               dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(short_order),0) + 1 as short_order from ethnicity");

               String strAddEthnicity = "INSERT into ethnicity(eth_id,eth_cd,eth_nm,last_updated,eth_flag,short_order,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Ethnicity.EthnicityShortName.Replace("'", "''") + "',N'" + Ethnicity.EthnicityName.Replace("'", "''") + "',N'" + Ethnicity.LastUpdated + "',N'A'," + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "," + Ethnicity.SchoolID + ")";

               DataConnection.ConnectionPath.ExecuteCommand(strAddEthnicity);


           }
           else
           { }


       }

       //*********************Edit***************
       public void UpdateEthnicity(EthnicityBL Ethnicity)
       {

           if (server == "MSSQLServer")
           {
               string strUpdateEthnicity = "update ethnicity set eth_cd= N'" + Ethnicity.EthnicityShortName.Replace("'", "''") + "',eth_nm= N'" + Ethnicity.EthnicityName.Replace("'", "''") + "',last_updated= N'" + Ethnicity.LastUpdated + "' where eth_id=" + Ethnicity.EthnicityID + "";

               DataConnection.ConnectionPath.ExecuteCommand(strUpdateEthnicity);
           }

           if (server == "MySQL")
           {
               DateTime s = Convert.ToDateTime(Ethnicity.LastUpdated, DateTimeFormatInfo.InvariantInfo);
               Ethnicity.LastUpdated = s.ToString("yyyy/MM/dd");


               string strUpdateEthnicity = "update ethnicity set eth_cd= N'" + Ethnicity.EthnicityShortName.Replace("'", "''") + "',eth_nm= N'" + Ethnicity.EthnicityName.Replace("'", "''") + "',last_updated= N'" + Ethnicity.LastUpdated + "' where eth_id=" + Ethnicity.EthnicityID + "";

               DataConnection.ConnectionPath.ExecuteCommand(strUpdateEthnicity);

           }
           else
           {
           }


       }

       //********************Populate Record List[Search]****************

       public System.Data.DataTable EthnicityDetails(EthnicityBL Ethnicity)
       {
           DataTable dt = null;
           dt = DataConnection.ConnectionPath.Getdata("select * from ethnicity  where eth_flag='A' and sch_id="+ Ethnicity.SchoolID +" and eth_cd like '" + Ethnicity.EthnicityShortName + "%' and eth_nm like '" + Ethnicity.EthnicityName + "%' order by short_order").Tables[0];
           return dt;
       }

       public void UpdateEthnicityFlag(EthnicityBL Ethnicity)
       {

           if (server == "MSSQLServer")
           {
               string strUpdateEthnicity = "update ethnicity set eth_flag= N'" + Ethnicity.Flag + "',last_updated= N'" + Ethnicity.LastUpdated + "' where eth_id=" + Ethnicity.EthnicityID + "";

               DataConnection.ConnectionPath.ExecuteCommand(strUpdateEthnicity);
           }

           if (server == "MySQL")
           {
               DateTime s = Convert.ToDateTime(Ethnicity.LastUpdated, DateTimeFormatInfo.InvariantInfo);
               Ethnicity.LastUpdated = s.ToString("yyyy/MM/dd");


               string strUpdateEthnicity = "update ethnicity set last_updated= N'" + Ethnicity.LastUpdated + "',eth_flag=N'" + Ethnicity.Flag + "' where eth_id=" + Ethnicity.EthnicityID + "";

               DataConnection.ConnectionPath.ExecuteCommand(strUpdateEthnicity);

           }
           else
           {
           }


       }

       public void UpdateEthnicityShortOrder(EthnicityBL Ethnicity)
       {

           string strUpdateNotesOrder = "update ethnicity set short_order= N'" + Ethnicity.ShortOrder.Replace("'", "''") + "' where eth_id=" + Ethnicity.EthnicityID + "";

           DataConnection.ConnectionPath.ExecuteCommand(strUpdateNotesOrder);

       }


    }
}
